# -*- coding: utf-8 -*-

# Author: 一根鱼骨棒 Email 775639471@qq.com
# Date: 2023-11-14 13:28:31
# LastEditTime: 2024-03-06 21:30:11
# LastEditors: 一根鱼骨棒
# Description: 本开源代码使用GPL 3.0协议
# Software: VScode
# Copyright 2023 迷舍

from flask import Flask
from flask_cors import CORS
from flask import render_template
from flask import request
import json

import sqlite3
import flask
conn = sqlite3.connect('D:\\code\\route\\data.sqlite3',
                       check_same_thread=False)
Db = conn.cursor()
'''
# 执行sql语句
# city = Db.execute('select name from city').fetchall()
# 更改（包括增加、更新和删除）数据时，我们要先调用 execute() 方法更改数据库中的数据，
# 然后调用 Connection 对象的 commit() 方法进行提交，否则操作不会被保存：
# positon = Db.execute('')
# conn.commit()
'''
app = Flask(__name__, template_folder="templates", static_folder="static")
CORS(app)
route = Db.execute(
    'select date,method,start,end,start_x,start_y,end_x,end_y,remark from route order by date asc').fetchall()
city = Db.execute('select name from city').fetchall()


@app.route('/')
def index():
    # 初始化游标 避免出现 sqlite3.ProgrammingError: Cannot operate on a closed database.
    Db = conn.cursor()
    return render_template('index.html')


@app.route('/getCityList')
def get_city_list():

    result = []
    for eachone in city:
        result.append(eachone[0])
    return result


@app.route('/getCity')
def get_city():
    '''
    返回到达过的城市列表，带坐标
    '''

    result = []

    for eachone in route:
        result.append([eachone[2], eachone[4], eachone[5]])
        result.append([eachone[3], eachone[6], eachone[7]])
    result = list(set(tuple(x) for x in result))

    return result


@app.route('/getRoute')
def get_route():
    result = []
    for eachone in route:
        result.append(eachone)

    return result


@app.route('/add', methods=['POST', 'GET'])
def add_route():
    # name=request.values.get("city")
    data = request.values
    start = data.get('start')
    end = data.get('end')
    method = data.get('method')
    datetime = data.get('date')
    remark = data.get('remark')
    if data.get('muti'):
        muti = data.get('muti')
    else:
        muti = 0
    # number=data.get('number')
    start_str = 'select x,y from city where name="' + start+'"'
    end_str = 'select x,y from city where name="' + end+'"'
    # 重置游标
    start_cursor = conn.cursor()
    start_positon = start_cursor.execute(start_str).fetchall()

    start_x = start_positon[0][0]
    start_y = start_positon[0][1]
    end_cursor = conn.cursor()
    end_positon = end_cursor.execute(end_str).fetchall()

    end_x = end_positon[0][0]
    end_y = end_positon[0][1]
    result = {"start": start, "end": end, 'start_x': start_x,
              "start_y": start_y, "end_x": end_x, "end_y": end_y}
    # 添加数据
    sql = 'INSERT INTO route (date,method,start,end,start_x,start_y,end_x,end_y,remark,muti)  values (' + \
        datetime+',"'+method+'","' + start+'","'+end+'",' + \
        start_x+','+start_y+','+end_x+','+end_y+',"'+remark+'",'+str(muti)+')'
    # positon = Db.execute('INSERT INFO route  values(?,?,?,?,?,?,?,?)',(datetime,method,start,end,start_x,start_y,end_x,end_y))

    Db = conn.cursor()
    positon = Db.execute(sql)
    conn.commit()
    # 重置游标
    return positon


@app.route('/list')
def list_route():

    print(route)

    return route


@app.route('/edit')
def edit_route():
    '''
    修改路径数据
    '''
    print(route)

    return route

if __name__ == '__main__':
    app.debug = True
    # flask 不支持中文名称的电脑
    app.run(host='0.0.0.0', port=80)
